<?php

    class Commande  {
        //Alias sur table : c
        public $id_commande;
        public $adresse;
        public $panier;
        public $transporteur;
        public $moyen_paiement;
        public $statut;
        public $avoir;
        public $prix_total_ht;
        public $prix_total_ttc;
        public $frais_transporteur;
        public $numero_livraison;
        public $date_livraison;
        public $date_ajout;
        public $date_modif;
        
        

        //Constructeur complet
        //(Adresse)adresse, (Panier)panier, (Transporteur)transporteur, (MoyenPaiement)moyen_paiement, (Statut)statut, (Avoir)avoir
        public function __construct($id_commande, $adresse, $panier, $transporteur, $moyen_paiement, $statut, $avoir, $prix_total_ht, 
                                                                $prix_total_ttc, $frais_transporteur, $numero_livraison, $date_livraison, $date_ajout, $date_modif) {
                $this->setId($id_commande);
                $this->setAdresse($adresse);
                $this->setPanier($panier);
                $this->setTransporteur($transporteur);
                $this->setMoyenPaiement($moyen_paiement);
                $this->setStatut($statut);
                $this->setAvoir($avoir);
                $this->setPrixTotalHt($prix_total_ht);
                $this->setPrixTotalTtc($prix_total_ttc);
                $this->setFraisTransporteur($frais_transporteur);
                $this->setNumeroLivraison($numero_livraison);
                $this->setDateLivraison($date_livraison);
                $this->setDateAjout($date_ajout);
                $this->setDateModif($date_modif);
        }

        //Ajoute une ligne de Commande à la BDD
        //Paramètre : aucun
        //Retourne (Commande)
        public function add() {
            $commande = $this->panierDejaUtilise();
            if($commande instanceof Commande) {
                return $commande;
            }
            else {
                $id_avoir = 'NULL';
                if($this->avoir instanceof Avoir && $this->avoir->getId() != 0) {
                        $id_avoir = intval($this->avoir->getId());
                }

                $sql =  'INSERT INTO commande (id_adresse, id_panier, id_transporteur, id_moyen_paiement, id_statut, id_avoir, prix_total_ht, 
                        prix_total_ttc, frais_transporteur, numero_livraison, date_livraison, date_ajout, date_modif) VALUES
                        ('.intval($this->adresse->getId()).', '.intval($this->panier->getId()).', '.intval($this->transporteur->getId()).', 
                        '.intval($this->moyen_paiement->getId()).', '.intval($this->statut->getId()).', '.$id_avoir.', 
                        '.$this->prix_total_ht.', '.$this->prix_total_ttc.', '.intval($this->frais_transporteur).', 0, "0000-00-00", 
                        "'.$this->getDateAjout().'", "'.$this->getDateModif().'")';
                $this->setId(Db::Execute($sql));
                $this->paiement();
                return $this;
            }
        }

        //Modifie une ligne de Commande dans la BDD
        //Paramètre : aucun
        //Retourne (Commande)
        public function update() {
            $this->setDateModif(new DateTime());
            $sql =  'UPDATE commande
                    SET numero_livraison = '.intval($this->getNumeroLivraison()).', 
                        id_statut = '.intval($this->statut->getId()).', 
                        date_livraison = "'.$this->getDateLivraison().'", 
                        date_modif = "'.$this->getDateModif().'"
                    WHERE
                        id_commande = '.intval($this->getId());
            Db::Execute($sql);
            return $this;
        }

        //Regarde si le Panier est déja utilisé dans une autre commande
        //Paramètre : aucun
        //Retourne (Commande)
        public function panierDejaUtilise() {
            $sql =  'SELECT
                        c.id_commande as cid_commande, c.prix_total_ht as cprix_total_ht, c.prix_total_ttc as cprix_total_ttc, 
                        c.frais_transporteur as cfrais_transporteur, c.numero_livraison as cnumero_livraison, c.date_livraison as cdate_livraison,
                        c.date_ajout as cdate_ajout, c.date_modif as cdate_modif, 
                        a.id_adresse as aid_adresse, a.adr1 as aadr1, a.adr2 as aadr2, a.ville as aville, a.cp as acp, a.pays as apays, a.supprime as asupprime, 
                        a.valide as avalide, 
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, cl.mdp as clmdp, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide,
                        p.id_panier as pid_panier, p.date_ajout as pdate_ajout, p.date_modif as pdate_modif, 
                        cl2.id_client as cl2id_client, cl2.login as cl2login, cl2.nom as cl2nom, cl2.prenom as cl2prenom, 
                        cl2.mdp as clmdp, cl2.telephone as cl2telephone, cl2.mobile as cl2mobile, cl2.email as cl2email, 
                        cl2.date_naissance as cl2date_naissance, cl2.date_ajout as cl2date_ajout, cl2.date_modif as cl2date_modif, 
                        cl2.valide as cl2valide, cl2.mdp as cl2mdp, 
                        pro2.id_profil as pro2id_profil, pro2.libelle as pro2libelle, pro2.valide as pro2valide,
                        civ2.id_civilite as civ2id_civilite, civ2.libelle as civ2libelle, civ2.diminutif as civ2diminutif, civ2.valide as civ2valide,
                        t.id_transporteur as tid_transporteur, t.libelle as tlibelle, t.email as temail, t.franco_port as tfranco_port, 
                        t.frais_port as tfrais_port, t.date_ajout as tdate_ajout, t.date_modif, t.valide as tvalide, t.tel as ttel, t.date_modif as tdate_modif, 
                        tv.id_tva as tvid_tva, tv.taux as tvtaux,
                        mp.id_moyen_paiement as mpid_moyen_paiement, mp.libelle as mplibelle, mp.valide as mpvalide,
                        s.id_statut as sid_statut, s.libelle as slibelle, s.fin as sfin, s.valide as svalide,
                        av.id_avoir as avid_avoir, av.id_commande as avid_commande, av.montant as avmontant, av.valide as avvalide
                    FROM commande c
                    LEFT JOIN adresse a ON c.id_adresse = a.id_adresse
                    LEFT JOIN client cl ON a.id_client = cl.id_client 
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    LEFT JOIN panier p ON c.id_panier = p.id_panier
                    LEFT JOIN client cl2 ON p.id_client = cl2.id_client
                    LEFT JOIN profil pro2 ON cl2.id_profil = pro2.id_profil
                    LEFT JOIN civilite civ2 ON cl2.id_civilite = civ2.id_civilite
                    LEFT JOIN transporteur t ON c.id_transporteur = t.id_transporteur
                    LEFT JOIN tva tv ON t.id_tva = tv.id_tva
                    LEFT JOIN moyen_paiement mp ON c.id_moyen_paiement = mp.id_moyen_paiement
                    LEFT JOIN statut s ON c.id_statut = s.id_statut
                    LEFT JOIN avoir av ON c.id_avoir = av.id_avoir
                    WHERE 
                        c.id_panier = '.intval($this->getPanier()->getId());
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                $client = new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], $p['cltelephone'],
                                $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], $p['cldate_modif'], $p['clvalide']);
                $adresse = new Adresse($p['aid_adresse'], $client, $p['aadr1'], $p['aadr2'], $p['aville'], $p['acp'], $p['apays'], $p['asupprime'], 
                                                                $p['avalide']);

                $profil = new Profil($p['pro2id_profil'], $p['pro2libelle'], $p['pro2valide']);
                $civilite = new Civilite($p['civ2id_civilite'], $p['civ2libelle'], $p['civ2diminutif'], $p['civ2valide']);
                $client = new Client($p['cl2id_client'], $civilite, $profil, $p['cl2login'], $p['cl2mdp'], $p['cl2nom'], $p['cl2prenom'], $p['cl2telephone'],
                                $p['cl2mobile'], $p['cl2email'], $p['cl2date_naissance'], $p['cl2date_ajout'], $p['cl2date_modif'], $p['cl2valide']);
                $panier = new Panier($p['pid_panier'], $client, $p['pdate_ajout'], $p['pdate_modif']);

                $tva = new Tva($p['tvid_tva'], $p['tvtaux']);
                $transporteur = new Transporteur($p['tid_transporteur'], $p['tlibelle'], $p['ttel'], $p['temail'], $p['tfranco_port'], $p['tfrais_port'], $tva,
                                                                                $p['tdate_ajout'], $p['tdate_modif'], $p['tvalide']);

                $moyen_paiement = new MoyenPaiement($p['mpid_moyen_paiement'], $p['mplibelle'], $p['mpvalide']);
                $statut = new Statut($p['sid_statut'], $p['slibelle'], $p['sfin'], $p['svalide']);
                $avoir = new Avoir($p['avid_avoir'], $p['avid_commande'], $p['avmontant'], $p['avvalide']);
                return new Commande($p['cid_commande'], $adresse, $panier, $transporteur, $moyen_paiement, $statut, $avoir, $p['cprix_total_ht'], 
                                                        $p['cprix_total_ttc'], $p['cfrais_transporteur'], $p['cnumero_livraison'], $p['cdate_livraison'], 
                                                        $p['cdate_ajout'], $p['cdate_modif']);
            }
            return null;
        }
        
        //Permet l'ajout de tout les lignes de CommandeDetail
        //Paramètre : aucun
        //Retourne collection de CommandeDetail
        public function paiement() {
            $panier_details = $this->panier->getPanierDetails();
            foreach($panier_details as $pd) {
                $promotion = $pd->getProduit()->getPromotion();
                if($pd->getVentePriveeDetail() instanceof VentePriveeDetail) {
                    $promotion = $pd->getVentePriveeDetail()->getPromotion();
                }
                
                $commande_detail = new CommandeDetail(0, $this, $pd->getProduit(), $pd->getQuantite(), $pd->getProduit()->getLibelle(), 
                                                    $pd->getProduit()->getReference(), $pd->getProduit()->getPrixUnit(), 
                                                    $pd->getProduit()->getTva(), $promotion);
                $commande_detail->add();
                $tabRetour[] = $commande_detail;
            }
            $total_client = $this->adresse->getClient()->getTotalAchat();
            if($total_client > 1500.00) {
                $this->adresse->getClient()->setVIP();
            }
            
            return $tabRetour;
        }
        
        //Change le Statut de la Commande
        //Paramètre : (Statut)statut
        //Retourne (CommandeHistorique)
        public function addCommandeHistorique($statut) {
            if($statut instanceof Statut && $this->statut != $statut) {
                $commande_historique = new CommandeHistorique(0, $this, $this->statut, new DateTime());
                $this->setStatut($statut);
                if($this->statut->getFin() == 1 && $this->getNumeroLivraison() == 0) {
                    $this->livree();
                }
                $this->update();
                return $commande_historique->add();
            }
            
            return null;
        }
        
        //Ajoute les données de livraison de la commande
        //Paramètre : aucun
        //Retourne (Commande)
        public function livree() {
            $this->setNumeroLivraison($this->incrementationNumeroLivraison());
            $this->setDateLivraison(new DateTime());
            return $this->update();
        }
        
        //Autoincrémente le numéro de livraison de la Commande
        //Paramètre : aucun
        //Retourne (int)
        public function incrementationNumeroLivraison() {
            $sql =  'SELECT 
                        MAX(c.numero_livraison) as max
                    FROM commande c';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                return intval($p['max'] + 1);
            }
            return 1;
        }
        
        //Récupère l'historique de la commande
        //Paramètre : aucun
        //Retourne collection de CommandeHistorique
        public function getCommandeHistoriques() {
            $sql =  'SELECT
                        ch.id_commande_historique as chid_commande_historique, ch.date_ajout as chdate_ajout,
                        s.id_statut as sid_statut, s.libelle as slibelle, s.fin as sfin, s.valide as svalide
                    FROM commande_historique ch
                    LEFT JOIN statut s ON ch.id_statut = s.id_statut
                    WHERE
                        ch.id_commande = '.intval($this->getId()).'
                    ORDER BY ch.id_commande_historique DESC';
            $ps = Db::Execute($sql, true);
            $tabRetour = array();
            foreach($ps as $p) {
                $statut = new Statut($p['sid_statut'], $p['slibelle'], $p['sfin'], $p['svalide']);
                $tabRetour[] = new CommandeHistorique($p['chid_commande_historique'], $this, $statut, $p['chdate_ajout']); 
            }

            return $tabRetour;
        }
             
        //Récupère les produits d'une commande
        //Paramètre : Aucun
        //Retourne une collection de CommandeDetail
        public function getCommandeDetails(){
            $sql =  'SELECT
                        cd.id_commande_detail as cdid_commande_detail, cd.quantite as cdquantite, cd.produit_prix_unit as cdproduit_prix_unit,
                        cd.promotion as cdpromotion, cd.produit_reference as cdproduit_reference, cd.produit_libelle as cdproduit_libelle,
                        tv.id_tva as tvid_tva, tv.taux as tvtaux, 
                        ma.id_marque as maid_marque, ma.libelle as malibelle, ma.email as maemail, ma.tel as matel, ma.image as maimage, 
                        ma.date_ajout as madate_ajout, ma.date_modif as madate_modif, ma.valide as mavalide, 
                        prod.id_produit as prodid_produit, prod.libelle as prodlibelle, prod.reference as prodreference, prod.description as proddescription,
                        prod.description_longue as proddescription_longue, prod.prix_unit as prodprix_unit, prod.promotion as prodpromotion, 
                        prod.vente_privee as prodvente_privee, prod.date_ajout as proddate_ajout, prod.date_modif as proddate_modif, 
                        prod.valide as prodvalide, prod.stock as prodstock, 
                        tv2.id_tva as tv2id_tva, tv2.taux as tv2taux
                    FROM commande_detail cd
                    LEFT JOIN produit prod ON cd.id_produit = prod.id_produit
                    LEFT JOIN marque ma ON prod.id_marque = ma.id_marque
                    LEFT JOIN tva tv ON prod.id_tva = tv.id_tva
                    LEFT JOIN tva tv2 ON cd.produit_id_tva = tv2.id_tva
                    WHERE
                        cd.id_commande = '.intval($this->getId());
            $ps = Db::Execute($sql, true);
            $tabRetour = array();
            foreach($ps as $p) {
                $marque = new Marque($p['maid_marque'], $p['malibelle'], $p['maemail'], $p['matel'], $p['maimage'], $p['madate_ajout'],
                                                        $p['madate_modif'], $p['mavalide']);
                $tva = new Tva($p['tvid_tva'], $p['tvtaux']);							
                $produit = new Produit($p['prodid_produit'], $marque, $p['prodlibelle'], $p['prodreference'], $p['proddescription'], 
                                        $p['proddescription_longue'], $p['prodstock'], 
                                        $p['prodprix_unit'], $tva, $p['prodpromotion'], $p['prodvente_privee'], $p['proddate_ajout'], 
                                        $p['proddate_modif'], $p['prodvalide']);
                $tva = new Tva($p['tv2id_tva'], $p['tv2taux']);	
                $tabRetour[] = new CommandeDetail($p['cdid_commande_detail'], $this, $produit, $p['cdquantite'], $p['cdproduit_libelle'], 
                                                $p['cdproduit_reference'], $p['cdproduit_prix_unit'], $tva, $p['cdpromotion']);
            }                   
            return $tabRetour;
                          
        }
        //Récupère le chiffre d'affaire du site à partir de dates données
        //Paramètres : (DATETIME)date_debut et (DATETIME)date_fin en option
        //Retourne le CA du site
        public static function getCA($date_debut, $date_fin= null){                    
            $sql =  'SELECT SUM(c.prix_total_ht)
                        FROM commande c
                        WHERE 
                        c.date_ajout >= "'.$date_debut->format('Y-m-d').' 00:00:00"';
            if($date_fin instanceof DateTime) {
                $sql .= ' AND c.date_ajout <= "'.$date_fin->format('Y-m-d').' 23:59:59"';                            
            }           
            
            $ps = Db::Execute($sql, true);                                              

            return $ps[0];                                                
        }
        
        //Récupère toutes les commandes de la BDD
        //Paramètre : aucun
        //Retourne collection de Commande
        public static function getCommandes() {
            $sql =  'SELECT
                        c.id_commande as cid_commande, c.prix_total_ht as cprix_total_ht, c.prix_total_ttc as cprix_total_ttc, 
                        c.frais_transporteur as cfrais_transporteur, c.numero_livraison as cnumero_livraison, c.date_livraison as cdate_livraison,
                        c.date_ajout as cdate_ajout, c.date_modif as cdate_modif, 
                        a.id_adresse as aid_adresse, a.adr1 as aadr1, a.adr2 as aadr2, a.ville as aville, a.cp as acp, a.pays as apays, a.supprime as asupprime, 
                        a.valide as avalide, 
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, cl.mdp as clmdp, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide,
                        p.id_panier as pid_panier, p.date_ajout as pdate_ajout, p.date_modif as pdate_modif, 
                        cl2.id_client as cl2id_client, cl2.login as cl2login, cl2.nom as cl2nom, cl2.prenom as cl2prenom, 
                        cl2.mdp as clmdp, cl2.telephone as cl2telephone, cl2.mobile as cl2mobile, cl2.email as cl2email, 
                        cl2.date_naissance as cl2date_naissance, cl2.date_ajout as cl2date_ajout, cl2.date_modif as cl2date_modif, 
                        cl2.valide as cl2valide, cl2.mdp as cl2mdp, 
                        pro2.id_profil as pro2id_profil, pro2.libelle as pro2libelle, pro2.valide as pro2valide,
                        civ2.id_civilite as civ2id_civilite, civ2.libelle as civ2libelle, civ2.diminutif as civ2diminutif, civ2.valide as civ2valide,
                        t.id_transporteur as tid_transporteur, t.libelle as tlibelle, t.email as temail, t.franco_port as tfranco_port, 
                        t.frais_port as tfrais_port, t.date_ajout as tdate_ajout, t.date_modif, t.valide as tvalide, t.tel as ttel, t.date_modif as tdate_modif, 
                        tv.id_tva as tvid_tva, tv.taux as tvtaux,
                        mp.id_moyen_paiement as mpid_moyen_paiement, mp.libelle as mplibelle, mp.valide as mpvalide,
                        s.id_statut as sid_statut, s.libelle as slibelle, s.fin as sfin, s.valide as svalide,
                        av.id_avoir as avid_avoir, av.id_commande as avid_commande, av.montant as avmontant, av.valide as avvalide
                    FROM commande c
                    LEFT JOIN adresse a ON c.id_adresse = a.id_adresse
                    LEFT JOIN client cl ON a.id_client = cl.id_client 
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    LEFT JOIN panier p ON c.id_panier = p.id_panier
                    LEFT JOIN client cl2 ON p.id_client = cl2.id_client
                    LEFT JOIN profil pro2 ON cl2.id_profil = pro2.id_profil
                    LEFT JOIN civilite civ2 ON cl2.id_civilite = civ2.id_civilite
                    LEFT JOIN transporteur t ON c.id_transporteur = t.id_transporteur
                    LEFT JOIN tva tv ON t.id_tva = tv.id_tva
                    LEFT JOIN moyen_paiement mp ON c.id_moyen_paiement = mp.id_moyen_paiement
                    LEFT JOIN statut s ON c.id_statut = s.id_statut
                    LEFT JOIN avoir av ON c.id_avoir = av.id_avoir
                    ORDER BY c.id_commande';
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                $client = new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], $p['cltelephone'],
                                $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], $p['cldate_modif'], $p['clvalide']);
                $adresse = new Adresse($p['aid_adresse'], $client, $p['aadr1'], $p['aadr2'], $p['aville'], $p['acp'], $p['apays'], $p['asupprime'], 
                                                                $p['avalide']);

                $profil = new Profil($p['pro2id_profil'], $p['pro2libelle'], $p['pro2valide']);
                $civilite = new Civilite($p['civ2id_civilite'], $p['civ2libelle'], $p['civ2diminutif'], $p['civ2valide']);
                $client = new Client($p['cl2id_client'], $civilite, $profil, $p['cl2login'], $p['cl2mdp'], $p['cl2nom'], $p['cl2prenom'], $p['cl2telephone'],
                                $p['cl2mobile'], $p['cl2email'], $p['cl2date_naissance'], $p['cl2date_ajout'], $p['cl2date_modif'], $p['cl2valide']);
                $panier = new Panier($p['pid_panier'], $client, $p['pdate_ajout'], $p['pdate_modif']);

                $tva = new Tva($p['tvid_tva'], $p['tvtaux']);
                $transporteur = new Transporteur($p['tid_transporteur'], $p['tlibelle'], $p['ttel'], $p['temail'], $p['tfranco_port'], $p['tfrais_port'], $tva,
                                                                                $p['tdate_ajout'], $p['tdate_modif'], $p['tvalide']);

                $moyen_paiement = new MoyenPaiement($p['mpid_moyen_paiement'], $p['mplibelle'], $p['mpvalide']);
                $statut = new Statut($p['sid_statut'], $p['slibelle'], $p['sfin'], $p['svalide']);

                $avoir = new Avoir($p['avid_avoir'], $p['avid_commande'], $p['avmontant'], $p['avvalide']);
                $tabRetour[] = new Commande($p['cid_commande'], $adresse, $panier, $transporteur, $moyen_paiement, $statut, $avoir, $p['cprix_total_ht'], 
                                                        $p['cprix_total_ttc'], $p['cfrais_transporteur'], $p['cnumero_livraison'], $p['cdate_livraison'], 
                                                        $p['cdate_ajout'], $p['cdate_modif']);
            }
            return $tabRetour;
        }
        
        //Crée un objet Commande grâce à son ID
        //Paramètre : (int)id
        //Retourne (Commande)
        public static function rechercheCommande($id) {
            $sql =  'SELECT
                        c.id_commande as cid_commande, c.prix_total_ht as cprix_total_ht, c.prix_total_ttc as cprix_total_ttc, 
                        c.frais_transporteur as cfrais_transporteur, c.numero_livraison as cnumero_livraison, c.date_livraison as cdate_livraison,
                        c.date_ajout as cdate_ajout, c.date_modif as cdate_modif, 
                        a.id_adresse as aid_adresse, a.adr1 as aadr1, a.adr2 as aadr2, a.ville as aville, a.cp as acp, a.pays as apays, a.supprime as asupprime, 
                        a.valide as avalide, 
                        cl.id_client as clid_client, cl.login as cllogin, cl.nom as clnom, cl.prenom as clprenom, 
                        cl.mdp as clmdp, cl.telephone as cltelephone, cl.mobile as clmobile, cl.email as clemail, 
                        cl.date_naissance as cldate_naissance, cl.date_ajout as cldate_ajout, cl.date_modif as cldate_modif, 
                        cl.valide as clvalide, cl.mdp as clmdp, 
                        pro.id_profil as proid_profil, pro.libelle as prolibelle, pro.valide as provalide,
                        civ.id_civilite as civid_civilite, civ.libelle as civlibelle, civ.diminutif as civdiminutif, civ.valide as civvalide,
                        p.id_panier as pid_panier, p.date_ajout as pdate_ajout, p.date_modif as pdate_modif, 
                        cl2.id_client as cl2id_client, cl2.login as cl2login, cl2.nom as cl2nom, cl2.prenom as cl2prenom, 
                        cl2.mdp as clmdp, cl2.telephone as cl2telephone, cl2.mobile as cl2mobile, cl2.email as cl2email, 
                        cl2.date_naissance as cl2date_naissance, cl2.date_ajout as cl2date_ajout, cl2.date_modif as cl2date_modif, 
                        cl2.valide as cl2valide, cl2.mdp as cl2mdp, 
                        pro2.id_profil as pro2id_profil, pro2.libelle as pro2libelle, pro2.valide as pro2valide,
                        civ2.id_civilite as civ2id_civilite, civ2.libelle as civ2libelle, civ2.diminutif as civ2diminutif, civ2.valide as civ2valide,
                        t.id_transporteur as tid_transporteur, t.libelle as tlibelle, t.email as temail, t.franco_port as tfranco_port, 
                        t.frais_port as tfrais_port, t.date_ajout as tdate_ajout, t.date_modif, t.valide as tvalide, t.tel as ttel, t.date_modif as tdate_modif, 
                        tv.id_tva as tvid_tva, tv.taux as tvtaux,
                        mp.id_moyen_paiement as mpid_moyen_paiement, mp.libelle as mplibelle, mp.valide as mpvalide,
                        s.id_statut as sid_statut, s.libelle as slibelle, s.fin as sfin, s.valide as svalide,
                        av.id_avoir as avid_avoir, av.id_commande as avid_commande, av.montant as avmontant, av.valide as avvalide
                    FROM commande c
                    LEFT JOIN adresse a ON c.id_adresse = a.id_adresse
                    LEFT JOIN client cl ON a.id_client = cl.id_client 
                    LEFT JOIN profil pro ON cl.id_profil = pro.id_profil
                    LEFT JOIN civilite civ ON cl.id_civilite = civ.id_civilite
                    LEFT JOIN panier p ON c.id_panier = p.id_panier
                    LEFT JOIN client cl2 ON p.id_client = cl2.id_client
                    LEFT JOIN profil pro2 ON cl2.id_profil = pro2.id_profil
                    LEFT JOIN civilite civ2 ON cl2.id_civilite = civ2.id_civilite
                    LEFT JOIN transporteur t ON c.id_transporteur = t.id_transporteur
                    LEFT JOIN tva tv ON t.id_tva = tv.id_tva
                    LEFT JOIN moyen_paiement mp ON c.id_moyen_paiement = mp.id_moyen_paiement
                    LEFT JOIN statut s ON c.id_statut = s.id_statut
                    LEFT JOIN avoir av ON c.id_avoir = av.id_avoir
                    WHERE 
                        c.id_commande = '.intval($id);
            $ps = Db::Execute($sql, true);
            foreach($ps as $p) {
                $profil = new Profil($p['proid_profil'], $p['prolibelle'], $p['provalide']);
                $civilite = new Civilite($p['civid_civilite'], $p['civlibelle'], $p['civdiminutif'], $p['civvalide']);
                $client = new Client($p['clid_client'], $civilite, $profil, $p['cllogin'], $p['clmdp'], $p['clnom'], $p['clprenom'], $p['cltelephone'],
                                $p['clmobile'], $p['clemail'], $p['cldate_naissance'], $p['cldate_ajout'], $p['cldate_modif'], $p['clvalide']);
                $adresse = new Adresse($p['aid_adresse'], $client, $p['aadr1'], $p['aadr2'], $p['aville'], $p['acp'], $p['apays'], $p['asupprime'], 
                                                                $p['avalide']);

                $profil = new Profil($p['pro2id_profil'], $p['pro2libelle'], $p['pro2valide']);
                $civilite = new Civilite($p['civ2id_civilite'], $p['civ2libelle'], $p['civ2diminutif'], $p['civ2valide']);
                $client = new Client($p['cl2id_client'], $civilite, $profil, $p['cl2login'], $p['cl2mdp'], $p['cl2nom'], $p['cl2prenom'], $p['cl2telephone'],
                                $p['cl2mobile'], $p['cl2email'], $p['cl2date_naissance'], $p['cl2date_ajout'], $p['cl2date_modif'], $p['cl2valide']);
                $panier = new Panier($p['pid_panier'], $client, $p['pdate_ajout'], $p['pdate_modif']);

                $tva = new Tva($p['tvid_tva'], $p['tvtaux']);
                $transporteur = new Transporteur($p['tid_transporteur'], $p['tlibelle'], $p['ttel'], $p['temail'], $p['tfranco_port'], $p['tfrais_port'], $tva,
                                                                                $p['tdate_ajout'], $p['tdate_modif'], $p['tvalide']);

                $moyen_paiement = new MoyenPaiement($p['mpid_moyen_paiement'], $p['mplibelle'], $p['mpvalide']);
                $statut = new Statut($p['sid_statut'], $p['slibelle'], $p['sfin'], $p['svalide']);
                $avoir = new Avoir($p['avid_avoir'], $p['avid_commande'], $p['avmontant'], $p['avvalide']);
                return new Commande($p['cid_commande'], $adresse, $panier, $transporteur, $moyen_paiement, $statut, $avoir, $p['cprix_total_ht'], 
                                                        $p['cprix_total_ttc'], $p['cfrais_transporteur'], $p['cnumero_livraison'], $p['cdate_livraison'], 
                                                        $p['cdate_ajout'], $p['cdate_modif']);
            }
            return null;
        }
		
        //GETTERS ET SETTERS
        public function getId() {return $this->id_commande;}
        public function getAdresse() {return $this->adresse;}
        public function getPanier() {return $this->panier;}
        public function getTransporteur() {return $this->transporteur;}
        public function getMoyenPaiement() {return $this->moyen_paiement;}
        public function getStatut() {return $this->statut;}
        public function getAvoir() {return $this->avoir;}
        public function getPrixTotalHt() {return $this->prix_total_ht;}
        public function getPrixTotalTtc() {return $this->prix_total_ttc;}
        public function getFraisTransporteur() {return $this->frais_transporteur;}
        public function getNumeroLivraison() {return $this->numero_livraison;}
        public function getDateLivraison($format = 'Y-m-d') {return Tools::getterDate($this->date_livraison, $format);}
        public function getDateAjout($format = 'Y-m-d H:i:s') {return Tools::getterDate($this->date_ajout, $format);}
        public function getDateModif($format = 'Y-m-d H:i:s') {return Tools::getterDate($this->date_modif, $format);}

        public function setId($temp) {$this->id_commande = intval($temp);}
        public function setAdresse($temp) {$this->adresse = $temp;}
        public function setPanier($temp) {$this->panier = $temp;}
        public function setTransporteur($temp) {$this->transporteur = $temp;}
        public function setMoyenPaiement($temp) {$this->moyen_paiement = $temp;}
        public function setStatut($temp) {$this->statut = $temp;}
        public function setAvoir($temp) {$this->avoir = $temp;}
        public function setPrixTotalHt($temp) {$this->prix_total_ht = $temp;}
        public function setPrixTotalTtc($temp) {$this->prix_total_ttc = $temp;}
        public function setFraisTransporteur($temp) {$this->frais_transporteur = $temp;}
        public function setNumeroLivraison($temp) {$this->numero_livraison = intval($temp);}
        public function setDateLivraison($temp) {$this->date_livraison = Tools::setterDate($temp);}
        public function setDateAjout($temp) {$this->date_ajout = Tools::setterDate($temp);}
        public function setDateModif($temp) {$this->date_modif = Tools::setterDate($temp);}
    }
?>